'use strict';

const Service = require('egg').Service;
const Sequelize = require('sequelize');
const Op = Sequelize.Op;

class PlaylistService extends Service {
  // 歌单列表
  async page(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1,
        private:0
      },
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = playlist.id
                    AND
                    collection.type = 4
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = playlist.id
                    AND
                    likes.type = 4
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = playlist.id
                    AND
                    comment.type = 4
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','status','private']
      },
      include: [
        {
          model: ctx.model.User,
          as: 'user',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ],
      distinct: true
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = playlist.id
            AND
            collection.type = 4
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = playlist.id
              AND
              likes.type = 4
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    if(query.keyword){
      config.where.name = {
        [Op.like]:`%${query.keyword}%`
      }
    }
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Playlist.findAndCountAll(config);
  }

  // 歌单详情
  async detail(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id,
        status:1,
        private:0
      },
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = playlist.id
                    AND
                    collection.type = 4
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = playlist.id
                    AND
                    likes.type = 4
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = playlist.id
                    AND
                    comment.type = 4
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','status','private']
      },
      include: [
        {
          model: ctx.model.User,
          as: 'user',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = playlist.id
            AND
            collection.type = 4
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = playlist.id
              AND
              likes.type = 4
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Playlist.findOne(config);
  }

  // 歌单播放量加1
  async addPlayNum(data) {
    const { ctx } = this;
    const [results, metadata] = await ctx.model.query(
      'UPDATE playlist SET playNum=playNum+1 WHERE id = ?',{
      replacements: [data.id]
    });
    return results;
  }

  // 歌单评论列表
  async commentPage(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        cid:query.cid,
        type:4,
        status:1
      },
      order:[
        ['createTime', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = comment.id
                      AND
                      likes.type = 5
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment AS c
                  WHERE
                      c.pid = comment.id
                      AND
                      c.type = 4
                      AND
                      c.isDel = 0
                      AND
                      c.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','type','status','uid'],
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }],
      distinct: true
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = comment.id
              AND
              likes.type = 5
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    config.where.pid = query.pid?query.pid:0;
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Comment.findAndCountAll(config);
  }

  // 歌单评论/回复
  async comment(data) {
    const { ctx } = this;
    return await ctx.model.Comment.create({
      cid:data.cid,
      pid:data.pid?data.pid:0,
      content:data.content,
      type:4,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime(),
      updateTime:new Date().getTime(),
      delTime:0
    });
  }

  // 歌单评论是否已点赞
  async isCommentLikes(data){
    const { ctx } = this;
    let config = {
      where:{
        lid:data.lid,
        type:5,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Likes.findOne(config);
  }

  // 歌单评论点赞
  async doCommentLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.create({
      lid:data.lid,
      type:5,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌单评论取消点赞
  async doClearCommentLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.destroy({
      where:{
        lid:data.lid,
        type:5,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌单是否已点赞
  async isLikes(data){
    const { ctx } = this;
    let config = {
      where:{
        lid:data.lid,
        type:4,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Likes.findOne(config);
  }

  // 歌单点赞
  async doLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.create({
      lid:data.lid,
      type:4,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌单取消点赞
  async doClearLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.destroy({
      where:{
        lid:data.lid,
        type:4,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌单是否已收藏
  async isCollection(data){
    const { ctx } = this;
    let config = {
      where:{
        cid:data.cid,
        type:4,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Collection.findOne(config);
  }

  // 歌单收藏
  async doCollection(data) {
    const { ctx } = this;
    return await ctx.model.Collection.create({
      cid:data.cid,
      type:4,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌单取消收藏
  async doClearCollection(data) {
    const { ctx } = this;
    return await ctx.model.Collection.destroy({
      where:{
        cid:data.cid,
        type:4,
        uid:ctx.request.header.uid
      }
    });
  }

  // 收藏的歌单
  async collectionPage(query) {
    const { ctx } = this;
    let collectionResult = await ctx.model.Collection.findAll({
      where:{
        type:4,
        uid:ctx.request.header.uid
      },
      attributes: ['cid']
    });
    let collectionList = [];
    for(let i of collectionResult){
      collectionList.push(i.cid);
    }
    let config = {
      where:{
        isDel:0,
        status:1,
        private:0
      },
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = playlist.id
                    AND
                    collection.type = 4
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = playlist.id
                    AND
                    likes.type = 4
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = playlist.id
                    AND
                    comment.type = 4
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [
            Sequelize.literal(`(
              SELECT COUNT(*)
              FROM collection
              WHERE
                collection.cid = playlist.id
                AND
                collection.type = 4
                AND
                collection.uid = ${ctx.request.header.uid}
            )`),
            'isCollection'
          ],
          [
            Sequelize.literal(`(
              SELECT COUNT(*)
              FROM likes
              WHERE
                  likes.lid = playlist.id
                  AND
                  likes.type = 4
                  AND
                  likes.uid = ${ctx.request.header.uid}
            )`),
            'isLikes'
          ],
          [Sequelize.col('user.name'), 'userName']
        ],
        exclude: ['isDel','delTime','status','private']
      },
      include: [
        {
          model: ctx.model.User,
          as: 'user',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ],
      distinct: true
    };
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Playlist.findAndCountAll(config);
  }

  // 歌单下歌曲列表
  async musicList(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id
      },
      attributes: ['musics']
    };
    let detailResult = await ctx.model.Playlist.findOne(config);
    if(detailResult){
      if(detailResult.musics&&JSON.parse(detailResult.musics) instanceof Array) {
        if (JSON.parse(detailResult.musics).length > 0) {
          let config = {
            where: {
              isDel: 0,
              id: {
                [Op.in]: JSON.parse(detailResult.musics)
              }
            },
            order: [
              [ 'createTime', 'DESC' ]
            ],
            attributes: {
              include: [
                [
                  Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
                  'collectionNum'
                ],
                [
                  Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
                  'likesNum'
                ],
                [
                  Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
              )`),
                  'commentNum'
                ],
                [ Sequelize.col('star.name'), 'starName' ],
                [ Sequelize.col('classify.name'), 'classifyName' ],
                [ Sequelize.col('album.name'), 'albumName' ]
              ],
              exclude: ['isDel','delTime','status','lyrics']
            },
            include: [
              {
                model: ctx.model.Star,
                as: 'star',
                where: {
                  isDel: 0
                },
                duplicating: false,
                required: false,
                attributes: []
              },
              {
                model: ctx.model.Classify,
                as: 'classify',
                where: {
                  isDel: 0
                },
                duplicating: false,
                required: false,
                attributes: []
              },
              {
                model: ctx.model.Album,
                as: 'album',
                where: {
                  isDel: 0
                },
                duplicating: false,
                required: false,
                attributes: []
              }
            ],
            distinct: true
          };
          if(ctx.request.header.uid){
            config.attributes.include.push([
              Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                  collection.cid = music.id
                  AND
                  collection.type = 1
                  AND
                  collection.uid = ${ctx.request.header.uid}
              )`),
              'isCollection'
            ]);
            config.attributes.include.push([
              Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = music.id
                    AND
                    likes.type = 1
                    AND
                    likes.uid = ${ctx.request.header.uid}
              )`),
              'isLikes'
            ]);
          }
          if(!query.pageNO){
            query.pageNO = 1;
          }
          if (!query.pageSize) {
            query.pageSize = 10;
          }
          config.offset = (query.pageNO - 1) * query.pageSize;
          config.limit = query.pageSize;
          return await ctx.model.Music.findAndCountAll(config);
        } else {
          return false;
        }
      }else{
        return false;
      }
    }else{
      return false;
    }
  }

  // 我的歌单列表
  async myPage(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        uid:ctx.request.header.uid
      },
      order:[
        ['createTime', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = playlist.id
                    AND
                    collection.type = 4
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = playlist.id
                    AND
                    likes.type = 4
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = playlist.id
                    AND
                    comment.type = 4
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ]
        ],
        exclude: ['isDel','delTime','uid']
      },
      distinct: true
    };
    if(query.keyword){
      config.where.name = {
        [Op.like]:`%${query.keyword}%`
      }
    }
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Playlist.findAndCountAll(config);
  }

  // 我的歌单详情
  async myDetail(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id,
        uid:ctx.request.header.uid
      },
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = playlist.id
                    AND
                    collection.type = 4
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = playlist.id
                    AND
                    likes.type = 4
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = playlist.id
                    AND
                    comment.type = 4
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ]
        ],
        exclude: ['isDel','delTime','uid']
      }
    };
    return await ctx.model.Playlist.findOne(config);
  }

  // 歌单添加
  async add(data) {
    const { ctx } = this;
    return await ctx.model.Playlist.create({
      name:data.name,
      private:data.private,
      desc:data.desc?data.desc:'',
      cover:data.cover?data.cover:'',
      musics:'[]',
      uid:ctx.request.header.uid,
      createTime:new Date().getTime(),
      updateTime:new Date().getTime(),
      delTime:0
    });
  }

  // 歌单编辑
  async edit(data) {
    const { ctx } = this;
    return await ctx.model.Playlist.update({
      name:data.name,
      private:data.private,
      desc:data.desc?data.desc:'',
      cover:data.cover?data.cover:'',
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌单删除
  async del(data) {
    const { ctx } = this;
    const t = await ctx.model.transaction();
    try{
      let [collectionDel,likeDel,commentDel,playlistDel] = await Promise.all(
        [
          // 删除收藏
          ctx.model.Collection.destroy({
            where: {
              type:4,
              cid:data.id
            },
            transaction:t
          }),
          // 删除点赞
          ctx.model.Likes.destroy({
            where: {
              type:4,
              lid:data.id
            },
            transaction:t
          }),
          // 删除评论
          ctx.model.Comment.update({
            isDel:1,
            delTime:new Date().getTime()
          },{
            where:{
              type:4,
              cid:data.id
            },
            transaction:t
          }),
          // 删除歌单
          ctx.model.Playlist.update({
            isDel:1,
            delTime:new Date().getTime()
          },{
            where:{
              id:data.id,
              uid:ctx.request.header.uid
            },
            transaction:t
          })
        ]
      );
      if(!playlistDel[0]){
        throw new Error('error');
      }
      await t.commit();
      return playlistDel;
    }catch(e){
      await t.rollback();
      return [0];
    }
  }

  // 歌单歌曲添加
  async addMusic(data) {
    const { ctx } = this;
    let musics = [];   
    try{
      let detail = await ctx.model.Playlist.findOne({
        attributes:['musics'],
        where:{
          id:data.id,
          uid:ctx.request.header.uid,
          isDel:0
        }
      });
      if(!detail){
        throw new Error('error');
      }
      musics = JSON.parse(detail.dataValues.musics);
      if(musics.indexOf(parseInt(data.mid))==-1){
        musics.push(parseInt(data.mid));
      }    
    }catch(e){
      return [0];
    }
    return await ctx.model.Playlist.update({
      musics:JSON.stringify(musics),
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌单歌曲删除
  async delMusic(data) {
    const { ctx } = this;
    let musics = [];   
    try{
      let detail = await ctx.model.Playlist.findOne({
        attributes:['musics'],
        where:{
          id:data.id,
          uid:ctx.request.header.uid,
          isDel:0
        }
      });
      if(!detail){
        throw new Error('error');
      }
      musics = JSON.parse(detail.dataValues.musics);
      for(let i=0;i<musics.length;i++){
        if(musics[i]==parseInt(data.mid)){
          musics.splice(i,1);
          break;
        }
      }  
    }catch(e){
      return [0];
    }
    return await ctx.model.Playlist.update({
      musics:JSON.stringify(musics),
      updateTime:new Date().getTime()
    },{
      where:{
        id:data.id,
        uid:ctx.request.header.uid
      }
    });
  }

  // 播放量排名前十歌单
  async getTopPlaylist(){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1,
        private:0
      },
      offset:0,
      limit:10,
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = playlist.id
                      AND
                      collection.type = 4
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = playlist.id
                      AND
                      likes.type = 4
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = playlist.id
                      AND
                      comment.type = 4
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('user.account'), 'account'],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime'],
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = playlist.id
            AND
            collection.type = 4
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = playlist.id
              AND
              likes.type = 4
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Playlist.findAll(config);
  }

  // 全文搜索
  async search(query){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1,
        private:0,
        name:{
          [Op.like]:`%${query.keyword}%`
        }
      },
      offset:0,
      limit:5,
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = playlist.id
                      AND
                      collection.type = 4
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = playlist.id
                      AND
                      likes.type = 4
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = playlist.id
                      AND
                      comment.type = 4
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime'],
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = playlist.id
            AND
            collection.type = 4
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = playlist.id
              AND
              likes.type = 4
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Playlist.findAll(config);
  }
}

module.exports = PlaylistService;
